﻿ ------创建人：王涛       创建时间：2010年8月28日 15：45-------
USE ASERDB
IF EXISTS(SELECT NAME FROM SYSOBJECTS WHERE NAME='Proc_GetEaer_AccidentExperts' AND TYPE='P')
BEGIN
  DROP PROCEDURE Proc_GetEaer_AccidentExperts
END
GO
CREATE PROCEDURE [dbo].[Proc_GetEaer_AccidentExperts]
	@pageIndex int,
	@pageSize int,
	@expertName varchar(50),
	@specialties varchar(50),
	@AccidentID varchar(10)
 AS
DECLARE @SQL nvarchar(1000)
DECLARE @RecordCount int,@P1 INT
DECLARE @Where VARCHAR(200)
IF @PageIndex >1 SET @PageIndex = (@PageIndex-1)*@PageSize +1
SET @Where = ''
IF ((@specialties IS NOT NULL) AND (RTRIM(LTRIM(@specialties))<>''))
BEGIN
  SET @Where = @Where +  ' and Specialty LIKE ''%'+ @specialties + '%'''
END
IF ((@expertName IS NOT NULL) AND (RTRIM(LTRIM(@expertName))<>''))
BEGIN
  SET @Where = @Where +  ' and ExpertName LIKE ''%'+ @expertName + '%'''
END
IF (@AccidentID IS NOT NULL)
BEGIN
  SET @Where = @Where +  ' and AccidentID = '+@AccidentID+''
END
IF ((LEN(@Where)>0) AND (LEFT(@Where,5)=' AND')) 
BEGIN
SET @Where = Right(@Where,LEN(@Where)-5)
END
SET @SQL = N'select * from Eaer_AccidentExperts ea left join Eaer_Experts ee on ee.ExpertID=ea.ExpertID  ORDER BY ea.ExpertID DESC'
IF (LEN(@Where)>0)
BEGIN
  SET @SQL = 'select * from Eaer_AccidentExperts ea left join Eaer_Experts ee on ee.ExpertID=ea.ExpertID WHERE ' + @Where + ' ORDER BY ea.ExpertID DESC'
END
EXEC sp_cursoropen @P1 OUTPUT, @SQL, @scrollopt = 1, @ccopt = 335873, @rowcount = @RecordCount OUTPUT 
IF (@P1 != 0)
BEGIN
  EXEC sp_cursorfetch @P1, 32, @PageIndex, @PageSize 
  EXEC sp_cursorclose @P1
END
select @RecordCount
PRINT @SQL
return @@ERROR